/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

import java.awt.event.ItemEvent;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.commons.fileupload.*;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

/**
 *
 * @author Windows
 */
@WebServlet(name = "WriteDB", urlPatterns = {"/admin/WriteDB"})
public class WriteDB extends HttpServlet {

    /** 
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
            /* TODO output your page here
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet WriteDB</title>");  
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet WriteDB at " + request.getContextPath () + "</h1>");
            out.println("</body>");
            out.println("</html>");
             */
        } finally {
            out.close();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /** 
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session=request.getSession();
        String login = (String) session.getAttribute("login");
        if (!login.equals("true")) {
            response.sendRedirect("login.jsp");
        return;
    }
        try {
            String caseString = request.getParameter("case");
            int caseI = Integer.parseInt(caseString);
            String driverName = "com.mysql.jdbc.Driver";
            String userName = "root";
            String userPasswd = "1";
            String dbName = "zjcwkj";
            String dbTable = null;
            String SQL = null;
            String action = null;
            String idString = null;
            String content = null;
            int id;
            String url = "jdbc:mysql://localhost/" + dbName + "?user=" + userName + "&password=" + userPasswd;
            Class.forName("com.mysql.jdbc.Driver");
            java.sql.Connection connection = DriverManager.getConnection(url);
            java.sql.Statement statement = connection.createStatement();

            switch (caseI) {
                case 4:
                    action = request.getParameter("action");
                    idString = request.getParameter("id");
                    id = Integer.parseInt(idString);
                    if (action.equals("delete")) {
                        SQL = "delete from syllabus where syllabusid=" + idString;
                    }
                    statement.execute(SQL);
                    break;
                case 5:
                    action = request.getParameter("action");
                    idString = request.getParameter("id");
                    id = Integer.parseInt(idString);
                    if (action.equals("delete")) {
                        SQL = "delete from courseware where coursewareid=" + idString;
                    }
                    statement.execute(SQL);
                    break;
                case 6:
                    idString = request.getParameter("id");
                    id = Integer.parseInt(idString);
                    SQL = "delete from video where videoid=" + idString;
                    statement.execute(SQL);
                    break;
                case 7:
                    idString = request.getParameter("id");
                    id = Integer.parseInt(idString);
                    SQL = "delete from exercise where exerciseid=" + idString;
                    statement.execute(SQL);
                    break;
                case 8:
                    idString = request.getParameter("id");
                    id = Integer.parseInt(idString);
                    SQL = "delete from practice where practiceid=" + idString;
                    statement.execute(SQL);
                    break;
                case 10:
                     idString = request.getParameter("id");
                         id = Integer.parseInt(idString);
                         SQL = "delete from qa where qaid=" + idString;
                         statement.execute(SQL); 
                    break;
                default:
                    break;
            }
             statement.close();
           response.sendRedirect(request.getHeader( "Referer"));
        } catch (SQLException ex) {
            Logger.getLogger(WriteDB.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(WriteDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /** 
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session=request.getSession();
        String login = (String) session.getAttribute("login");
        if (!login.equals("true")) {
            response.sendRedirect("login.jsp");
        return;
    }
        try {
            String caseString = request.getParameter("case");
            int caseI = Integer.parseInt(caseString);
            String driverName = "com.mysql.jdbc.Driver";
            String userName = "root";
            String userPasswd = "1";
            String dbName = "zjcwkj";
            String dbTable = null;
            String SQL = null;
            String idString = request.getParameter("id");
            String content = null;
            String pathString = null;
            String pathString1 = null;
            String nameString = null;
            String actionString = request.getParameter("action");
            String url = "jdbc:mysql://localhost/" + dbName + "?user=" + userName + "&password=" + userPasswd;
            Class.forName("com.mysql.jdbc.Driver");
            java.sql.Connection connection = DriverManager.getConnection(url);
            java.sql.Statement statement = connection.createStatement();
            FileItemFactory factory = null;
            ServletFileUpload upload = null;
            List<FileItem> fileItems = null;
            Iterator<FileItem> iter = null;

            switch (caseI) {
                case 1:
                    content = new String(request.getParameter("content").getBytes("ISO-8859-1"), "utf-8");
                    SQL = "UPDATE info SET home='" + content + "'";
                    statement.executeUpdate(SQL);
                    break;
                case 2:
                    content = new String(request.getParameter("content").getBytes("ISO-8859-1"), "utf-8");
                    SQL = "UPDATE info SET info='" + content + "'";
                    statement.executeUpdate(SQL);
                    break;
                case 3:
                    content = new String(request.getParameter("content").getBytes("ISO-8859-1"), "utf-8");
                    SQL = "UPDATE info SET teacher='" + content + "'";
                    statement.executeUpdate(SQL);
                    break;
                case 4:

                    factory = new DiskFileItemFactory();
                    upload = new ServletFileUpload(factory);
                    // 得到所有的表单域，它们目前都被当作FileItem
                    fileItems = upload.parseRequest(request);
                    iter = fileItems.iterator();
                    while (iter.hasNext()) {
                        FileItem item = (FileItem) iter.next();
                        if (!item.isFormField()) {
                            String fileName = item.getName();;
                            if (fileName != null) {

                                File file = new File(request.getServletContext().getRealPath("/") + "/files/" + item.getName());
                                pathString = "/zjcwkj/files/" + item.getName();;
                                file.createNewFile();
                                item.write(file);
                            }

                        } else {
                            nameString = new String(item.getString().getBytes("ISO-8859-1"), "UTF-8");
                        }
                    }
                    if (nameString == "") {
                        nameString = "无名称";
                    }
                    if (actionString.equals("edit")) {
                        SQL = "update syllabus set name='" + nameString + "',filename='" + pathString + "' where syllabusid=" + idString;
                    } else {
                        SQL = "insert into syllabus (name,filename) values('" + nameString + "','" + pathString + "')";
                    }
                    statement.execute(SQL);
                    break;
                case 5:
                    factory = new DiskFileItemFactory();
                    upload = new ServletFileUpload(factory);
                    // 得到所有的表单域，它们目前都被当作FileItem
                    fileItems = upload.parseRequest(request);
                    iter = fileItems.iterator();
                    while (iter.hasNext()) {
                        FileItem item = (FileItem) iter.next();
                        if (!item.isFormField()) {
                            String fileName = item.getName();
                            if (fileName != null) {
                                File file = new File(request.getServletContext().getRealPath("/") + "/files/" + item.getName());
                                if (pathString == null) {
                                    pathString = "/zjcwkj/files/" + item.getName();
                                } else {
                                    pathString1 = "/zjcwkj/files/" + item.getName();
                                }
                                file.createNewFile();
                                item.write(file);
                            }

                        } else {
                            nameString = new String(item.getString().getBytes("ISO-8859-1"), "UTF-8");
                        }
                    }
                    if (nameString == "") {
                        nameString = "无名称";
                    }
                    if (actionString.equals("edit")) {
                        SQL = "update courseware set name='" + nameString + "',coursename='" + pathString + "',planname='" + pathString1 + "'" + " where coursewareid=" + idString;
                    } else {
                        SQL = "insert into courseware (name,coursewarename,planname) values('" + nameString + "','" + pathString + "','" + pathString1 + "')";
                    }
                    statement.execute(SQL);

                    break;
                case 6:
                    String speakerString = null;
                    factory = new DiskFileItemFactory();
                    upload = new ServletFileUpload(factory);
                    // 得到所有的表单域，它们目前都被当作FileItem
                    fileItems = upload.parseRequest(request);
                    iter = fileItems.iterator();
                    while (iter.hasNext()) {
                        FileItem item = (FileItem) iter.next();
                        if (!item.isFormField()) {
                            String fileName = item.getName();
                            if (fileName != null) {
                                File file = new File(request.getServletContext().getRealPath("/") + "/files/" + item.getName());
                                pathString = "/zjcwkj/files/" + item.getName();
                                file.createNewFile();
                                item.write(file);
                            }

                        } else {
                            if (nameString == null) {
                                nameString = new String(item.getString().getBytes("ISO-8859-1"), "UTF-8");
                            } else {
                                speakerString = new String(item.getString().getBytes("ISO-8859-1"), "UTF-8");
                            }
                        }
                    }

                    if (nameString == "") {
                        nameString = "无名称";
                    }
                    if (speakerString == "") {
                        speakerString = "无名称";
                    }
                    if (actionString.equals("edit")) {
                        SQL = "update video set name='" + nameString + "',speakername='" + speakerString + "'," + "name='" + pathString + "' where videoid=" + idString;
                    } else {
                        SQL = "insert into video (name,speakername,videolocation) values('" + nameString + "','" + speakerString + "','" + pathString + "')";
                    }
                    statement.execute(SQL);
                    break;
                case 7:

                    factory = new DiskFileItemFactory();
                    upload = new ServletFileUpload(factory);
                    // 得到所有的表单域，它们目前都被当作FileItem
                    fileItems = upload.parseRequest(request);
                    iter = fileItems.iterator();
                    while (iter.hasNext()) {
                        FileItem item = (FileItem) iter.next();
                        if (!item.isFormField()) {
                            String fileName = item.getName();
                            if (fileName != null) {
                                File file = new File(request.getServletContext().getRealPath("/") + "/files/" + item.getName());
                                pathString = "/zjcwkj/files/" + item.getName();
                                file.createNewFile();
                                item.write(file);
                            }

                        } else {
                            nameString = new String(item.getString().getBytes("ISO-8859-1"), "UTF-8");
                        }
                    }
                    if (nameString == null) {
                        nameString = "无名称";
                    }
                    if (actionString.equals("edit")) {
                        SQL = "update exercise set name='" + nameString + "',exerciselocation='" + pathString + "' where exerciseid=" + idString;
                    } else {
                        SQL = "insert into exercise (name,exerciselocation) values('" + nameString + "','" + pathString + "')";
                    }
                    statement.execute(SQL);
                    break;
                case 8:

                    factory = new DiskFileItemFactory();
                    upload = new ServletFileUpload(factory);
                    // 得到所有的表单域，它们目前都被当作FileItem
                    fileItems = upload.parseRequest(request);
                    iter = fileItems.iterator();
                    while (iter.hasNext()) {
                        FileItem item = (FileItem) iter.next();
                        if (!item.isFormField()) {
                            String fileName = item.getName();
                            if (fileName != null) {
                                File file = new File(request.getServletContext().getRealPath("/") + "/files/" + item.getName());
                                pathString = "/zjcwkj/files/" + item.getName();
                                file.createNewFile();
                                item.write(file);
                            }

                        } else {
                            nameString = new String(item.getString().getBytes("ISO-8859-1"), "UTF-8");
                        }
                    }
                    if (nameString == null) {
                        nameString = "无名称";
                    }
                    if (actionString.equals("edit")) {
                        SQL = "update practice set name='" + nameString + "',practicelocation='" + pathString + "' where practiceid=" + idString;
                    } else {
                        SQL = "insert into practice (name,practicelocation) values('" + nameString + "','" + pathString + "')";
                    }
                    statement.execute(SQL);
                    break;
                case 9:
                    content = new String(request.getParameter("content").getBytes("ISO-8859-1"), "utf-8");
                    SQL = "UPDATE info SET material='" + content + "'";
                    statement.executeUpdate(SQL);
                    break;
                case 10:
                    
                    content = new String(request.getParameter("content").getBytes("ISO-8859-1"), "utf-8");
                    if(actionString.equals("add"))
                    SQL = "insert into qa (question,flag) value('" + content + "',0)";
                    else
                    {
                        SQL="update qa set anwser='"+content+"',flag=1 where qaid="+idString;
                    }
                    
                    break;
            }
            statement.close();
              response.sendRedirect(request.getHeader( "Referer"));
        } catch (Exception ex) {
            Logger.getLogger(WriteDB.class.getName()).log(Level.SEVERE, null, ex);
        }


    }

    /** 
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}
